import pymysql

# 1. 连接数据库
def connect_db():
    try:
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='testdb',
            charset='utf8'
        )
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return None

# 2. 创建表
def create_table(conn):
    try:
        with conn.cursor() as cursor:
            sql = """
            CREATE TABLE IF NOT EXISTS person(
                username VARCHAR(20),
                age INT,
                sex CHAR(4),
                high DOUBLE(3,2)
            )
            """
            cursor.execute(sql)
        conn.commit()
        print("表创建成功")
    except Exception as e:
        print(f"创建表失败: {e}")
# 3. 输入人员信息
def input_person_info():
    persons = []
    for i in range(1, 11):
        print(f"\n请输入第 {i} 个人的信息:")
        username = input("姓名: ").strip()
        age = int(input("年龄: "))
        sex = input("性别(男/女): ").strip()
        high = float(input("身高(米): "))
        persons.append((username, age, sex, high))
    return persons
# 4. 插入数据
def insert_data(conn, persons):
    try:
        with conn.cursor() as cursor:
            sql = "INSERT INTO person(username, age, sex, high) VALUES (%s, %s, %s, %s)"
            cursor.executemany(sql, persons)
        conn.commit()
        print(f"成功插入 {len(persons)} 条数据")
    except Exception as e:
        print(f"插入数据失败: {e}")
        conn.rollback()
# 主程序
def main():
    # 连接数据库
    conn = connect_db()
    if not conn:
        return
    try:
        # 创建表
        create_table(conn)
        # 输入数据
        persons = input_person_info()
        # 插入数据
        insert_data(conn, persons)
        # 查询验证
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM person")
            results = cursor.fetchall()
            print("\n数据库中的记录:")
            for row in results:
                print(row)
    finally:
        # 关闭连接
        conn.close()


if __name__ == "__main__":
    main()